package com.kaishengit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.kaishengit.entity.Project;
import com.kaishengit.entity.User;
import com.kaishengit.entity.UserProject;
import com.kaishengit.util.DBHelp;
import com.kaishengit.util.RowMapper;

public class UserProjectDao {
	private DBHelp<UserProject> db=new DBHelp<UserProject>();
	
	public List<UserProject> findByUserId(int offest, int pageSize,String userid){
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT t_user_id,tu.username,tu.password,tu.imgname,t_project_id,tp.name,tp.description,tp.createtime,role FROM t_user_project AS tup ");
		sql.append("LEFT JOIN t_user AS tu ON tup.t_user_id=tu.id ");
		sql.append("LEFT JOIN t_project AS tp ON tup.t_project_id=tp.id ");
		sql.append("WHERE tu.id=? ORDER BY tp.createtime DESC  LIMIT ?,?");
		return  db.executeQueryForList(sql.toString(),new UserProjectRowMapper(),userid,offest,pageSize);
	}
	public boolean save(UserProject up) {
		String sql="INSERT INTO t_user_project(t_user_id,t_project_id,role)VALUES(?,?,?)";
		return db.executeSQL(sql, up.getT_user_id(),up.getT_project_id(),up.getRole());
		
	}
	public UserProject findById(String userid,String projectid){
		StringBuilder sql=new StringBuilder();
		sql.append("SELECT t_user_id,tu.username,tu.password,tu.imgname,t_project_id,tp.name,tp.description,tp.createtime,role FROM t_user_project AS tup ");
		sql.append("LEFT JOIN t_user AS tu ON tup.t_user_id=tu.id ");
		sql.append("LEFT JOIN t_project AS tp ON tup.t_project_id=tp.id ");
		sql.append("WHERE tup.t_user_id=? AND tup.t_project_id=?");
		return db.executeQueryForObject(sql.toString(),new UserProjectRowMapper(),userid,projectid);
	}
	private class UserProjectRowMapper implements RowMapper<UserProject>{

		public UserProject mapRow(ResultSet rs) throws SQLException {
			UserProject up=new UserProject();
			
			up.setT_project_id(rs.getString("t_project_id"));
			up.setT_user_id(rs.getString("t_user_id"));
			up.setRole(rs.getString("role"));
			
			User user=new User();
			user.setId(rs.getString("t_user_id"));
			user.setUsername(rs.getString("username"));
			user.setPassword(rs.getString("password"));
			user.setImgname(rs.getString("imgname"));
			up.setUser(user);
			
			Project pro=new Project();
			pro.setId(rs.getString("t_project_id"));
			pro.setName(rs.getString("name"));
			pro.setDescription(rs.getString("description"));
			pro.setCreatetime(rs.getString("createtime"));
			up.setProject(pro);
			
			return up;
		}
		
	}
}
